<%@ page language="java" %>
<%@ taglib uri="http://struts.apache.org/tags-bean" prefix="bean" %>
<%@ taglib uri="http://struts.apache.org/tags-html" prefix="html" %>
<%@ taglib uri="http://struts.apache.org/tags-logic" prefix="logic" %>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html:html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
		<title>Insert title here</title>
	</head>
<body>

<%-- Import the java.sql package --%>
            <%@ page import="java.sql.*"%>
            <%-- -------- Open Connection Code -------- --%>
            <%       
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            Statement stmt = null;            
           try{
                // Registering Postgresql JDBC driver with the DriverManager
                Class.forName("org.postgresql.Driver");

                // Open a connection to the database using DriverManager
                conn = DriverManager.getConnection(
                		"jdbc:postgresql://localhost:5433/GradApp?" +
                        "user=postgres&password=20880130");       
      %>
       
      <%
      String AppChoice1 = request.getParameter("SpecApp");
      String AppChoice2 = request.getParameter("DiscApp");
      if (AppChoice1 != null){
    	  //SpecApp
    	  int spec = Integer.parseInt(AppChoice1);
    	  
          String QuaryString = "SELECT firstname,middle,lastname,id "+
                               "FROM  Students "+
                               "WHERE Students.specialization = ?"; 
          pstmt = conn.prepareStatement(QuaryString);
          pstmt.setInt(1,spec);
          rs = pstmt.executeQuery();
          %>
        	  
        	  <p>Applicant Name</p>
        	      	       	  
        	  <ul>
        	 <% while (rs.next()){ %>
        	  <li>
	           	  <html:link href="Applicant.jsp?Applicant=<%=rs.getInt(4)%>">
	        	  <%=rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)%>
	        	  </html:link>
        	  </li>
          	  <%   
          		}
      }
      else if (AppChoice2 != null)
      {
    	  //DiscApp
    	  int disc = Integer.parseInt(AppChoice2);
    	  
    	  
          String QuaryString = "SELECT firstname,middle,lastname,Students.id "+
                               "FROM  Students,Degrees "+
                               "WHERE Degrees.discipline = ? "+
                               "AND Degrees.students = Students.id";       
          pstmt = conn.prepareStatement(QuaryString);
          pstmt.setInt(1,disc);
          rs = pstmt.executeQuery();
          %> 
        	  <p>Applicant Name</p>      	       	  
        	 <% while (rs.next()){ %>
        	  <li>
	           	  <html:link href="Applicant.jsp?Applicant=<%=rs.getInt(4)%>">
	        	  <%=rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)%>
	        	  </html:link>
        	  </li>
          	  <%   
        	 }
    	  
      }  
      else
      {
      stmt = conn.createStatement();
      String QuaryString = "SELECT firstname,middle,lastname,id "+
                           "FROM  Students " ;                     
      rs = stmt.executeQuery(QuaryString);
      %>
    	  
    	  <p>Applicant Name</p>
    	      	       	  
    	  <ul>
    	 <% while (rs.next()){ %>
    	  <li>
	       	  <html:link href="Applicant.jsp?Applicant=<%=rs.getInt(4)%>">
	    	  <%=rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)%>
	    	  </html:link>
    	  </li>
      	  <%   
      		}
      }
     	  %>	    
          </ul>
        
      <%        
           } catch (SQLException e) {

               // Wrap the SQL exception in a runtime exception to propagate
               // it upwards
               throw new RuntimeException(e);
           }
           finally {
               // Release resources in a finally block in reverse-order of
               // their creation

               if (rs != null) {
                   try {
                       rs.close();
                   } catch (SQLException e) { } // Ignore
                   rs = null;
               }
               if (pstmt != null) {
                   try {
                       pstmt.close();
                   } catch (SQLException e) { } // Ignore
                   pstmt = null;
               }
               if (conn != null) {
                   try {
                       conn.close();
                   } catch (SQLException e) { } // Ignore
                   conn = null;
               }
           } 
   %>             

</body>
</html:html>